package com.sanqing.dao;

import com.sanqing.model.Reader;
import com.sanqing.model.*;

import javax.swing.*;
import java.io.*;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Vector;

//数据访问层(DAO层)
public class Dao {
    protected static int maxNum;
    protected static int maxDays;
    protected static double yajin;
    protected static String dbClassName = "com.mysql.jdbc.Driver";     //数据库连接驱动类
    /*protected static String dbUrl = "jdbc:mysql://39.96.83.106:3306/db_library?useUnicode=true&characterEncoding=utf8";  //数据库连接URL
    protected static String dbUser = "root";      //数据库用户名
    protected static String dbPwd = "y0lo";       //数据库密码*/
    private static Connection conn = null;      //数据库连接对象

    protected static String dbUrl;  //数据库连接URL
    protected static String dbUser;      //数据库用户名
    protected static String dbPwd;       //数据库密码

    private static Statement sql = null;//sql语句对象
    private static String[] columns = null;//列名
    private static String[] forms = null;//表名
    private static String where = null;//条件
    private static String newKV = null;//update语句中的键对值
    private static String[] values = null;//insert中的值

    private Dao() {//默认构造函数
        setURL();//从外部初始化连接
        try {
            if (conn == null) {                     //如果连接对象为空
                Class.forName(dbClassName);         //加载驱动类
                conn = DriverManager.getConnection
                        (dbUrl, dbUser, dbPwd);   //获得连接对象
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        try {
            sql = conn.createStatement();
        } catch (SQLException e) {
            System.out.println("声明sql语句对象失败！ " + e.getMessage());
        }
        String sql = "select * from tb_borrow_settings;";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            while (rs.next()) {
                maxNum = rs.getInt(1);
                yajin = rs.getDouble(2);
                maxDays = rs.getInt(3);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //从外部文件获取数据连接信息
    private void setURL() {
        File urlLink = new File("dbServer.txt");
        if (urlLink.exists()) {
            try {
                FileReader reader = new FileReader(urlLink);
                BufferedReader bufferedReader = new BufferedReader(reader);
                try {
                    dbUrl = "jdbc:mysql://" + bufferedReader.readLine() + "/";
                    dbUrl = dbUrl + bufferedReader.readLine() + "?useUnicode=true&characterEncoding=utf8";
                    dbUser = bufferedReader.readLine();
                    dbPwd = bufferedReader.readLine();
                    bufferedReader.close();
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static ResultSet executeQuery(String sql) {  //查询方法
        try {
            if (conn == null || conn.isClosed()) {
                new Dao();  //连接对象为空或连接失效，重新调用构造方法
            }
            return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(sql);  //执行查询
        } catch (SQLException e) {
            e.printStackTrace();
            return null;        //返回null值
        }
    }

    private static int executeUpdate(String sql) {   //更新方法
        try {
            if (conn == null || conn.isClosed()) new Dao();  //如果连接对象为空，则重新调用构造方法
            return conn.createStatement().executeUpdate(sql);   //执行更新
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public static void close() {     //关闭方法
        try {
            conn.close();   //关闭连接对象
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;      //设置连接对象为null值
        }
    }

    /*
     * 该方法检测用户信息是否合法
     * */
    public static Operater check(String name, String password) {
        Operater operater = new Operater();   //操作员信息对象
        byte[] digest = null;
        try {
            MessageDigest md5 = MessageDigest.getInstance("md5");
            digest = md5.digest(password.getBytes("utf-8"));
        } catch (NoSuchAlgorithmException e1) {
            e1.printStackTrace();
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        //16是表示转换为16进制数
        String md5Str = new BigInteger(1, digest).toString(16);
        String sql = "select * from tb_operator where operator_id='" + name + "'and password='" + md5Str + "'";   //查询字符串
        ResultSet rs = Dao.executeQuery(sql);

        try {
            while (rs.next()) {  //如果查询到了记录
                operater.setId(rs.getString("operator_id"));     //设置操作员编号
                operater.setName(rs.getString("name")); //设置操作员用户名
                operater.setGrade(rs.getInt("admin"));//设置操作员等级
                operater.setPassword(rs.getString("password"));//设置管理员密码
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Dao.close();    //关闭连接对象
        return operater;    //返回操作员信息对象
    }

    //获取图书类别
    public static ArrayList<BookType> selectBookCategory() {
        ArrayList<BookType> list = new ArrayList<BookType>();
        BookType bookType;
        String sql = "select id,typeName from tb_bookType;";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (rs != null) {
                while (rs.next()) {
                    bookType = new BookType();
                    bookType.setId(rs.getInt("id"));
                    bookType.setTypeName(rs.getString("typeName"));
                    list.add(bookType);
                }
            }
            rs.close();
            Dao.close();
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
        }
        return list;
    }

    //添加新订购
    public static boolean addNewBook(BookInfo bookInfo) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String dateText = sdf.format(bookInfo.getDate());
        String insertSql = "insert into tb_bookInfo values ('" + bookInfo.getISBN() + "'," +
                bookInfo.getTypeid() + ",'" + bookInfo.getBookname() + "','" + bookInfo.getWriter() +
                "','" + bookInfo.getTranslator() + "','" + bookInfo.getPublisher() + "','" + dateText +
                "'," + bookInfo.getPrice() + ", 0, " + bookInfo.getAll_count() + "," + bookInfo.getAll_count() + ");";
        String updateSql = "update tb_bookInfo set cur_count = "+bookInfo.getAll_count()+"+ all_count -all_count + cur_count," +
                "all_count = all_count + " + bookInfo.getAll_count() + " where ISBN='" + bookInfo.getISBN() + "';";
        String firstSql = "select count(*) from tb_bookInfo where ISBN='" + bookInfo.getISBN() + "';";
        ResultSet rs = Dao.executeQuery(firstSql);
        int flag = -1;
        try {
            while (rs.next()) {
                flag = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (flag == 0) {
            flag = Dao.executeUpdate(insertSql);
        } else {
            flag = Dao.executeUpdate(updateSql);
        }
        Dao.close();
        return flag > 0;
    }

    public static boolean addOrderBook(BookInfo bookInfo) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String dateText = sdf.format(bookInfo.getDate());
        String insertSql = "insert into tb_order_book values ('" + bookInfo.getISBN() + "'," +
                bookInfo.getTypeid() + ",'" + bookInfo.getBookname() + "','" + bookInfo.getWriter() +
                "','" + bookInfo.getTranslator() + "','" + bookInfo.getPublisher() + "','" + dateText +
                "'," + bookInfo.getPrice() + "," + bookInfo.getAll_count() + ",0);";
        String updateSql = "update tb_order_book set count = count + " + bookInfo.getAll_count() + ",cur_count = cur_count +" + bookInfo.getAll_count() + " where ISBN='" + bookInfo.getISBN() + "';";
        String firstSql = "select count(*) from tb_order_book where ISBN='" + bookInfo.getISBN() + "';";
        ResultSet rs = Dao.executeQuery(firstSql);
        int flag = -1;
        try {
            while (rs.next()) {
                flag = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (flag == 0) {
            flag = Dao.executeUpdate(insertSql);
        } else {
            flag = Dao.executeUpdate(updateSql);
        }
        Dao.close();
        return flag > 0;
    }

    //查询图书
    public static BookInfo selectBookInfo(String isbn) {
        BookInfo bookInfo = new BookInfo();
        String sql = "select * from tb_bookInfo where ISBN like '" + isbn + "';";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (!rs.next()) {
                return null;
            }
            bookInfo.setTypeid(rs.getInt("typeId"));
            bookInfo.setBookname(rs.getString("bookname"));
            bookInfo.setWriter((rs.getString("writer")));
            bookInfo.setTranslator(rs.getString("translator"));
            bookInfo.setPublisher(rs.getString("publisher"));
            bookInfo.setDate(rs.getDate("date"));
            bookInfo.setPrice(rs.getDouble("price"));
            bookInfo.setAll_count(rs.getInt("all_count"));
            bookInfo.setCur_count(rs.getInt("cur_count"));
            rs.close();
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
        }
        Dao.close();
        return bookInfo;
    }

    //修改图书
    public static boolean EditBook(BookInfo bookInfo) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String dateText = sdf.format(bookInfo.getDate());
        String updateSql = "update tb_bookInfo set bookname='" + bookInfo.getBookname() + "',writer='" +
                bookInfo.getWriter() + "',translator='" + bookInfo.getTranslator() + "',publisher='" +
                bookInfo.getPublisher() + "',date='" + dateText + "',price=" + bookInfo.getPrice() +
                ",cur_count=" + bookInfo.getAll_count() + "-all_count+cur_count,all_count=" + bookInfo.getAll_count() +
                " where ISBN='" + bookInfo.getISBN() + "';";
        int flag = Dao.executeUpdate(updateSql);
        Dao.close();
        return flag > 0;
    }

    public static Vector bookInfoHead(boolean flag) {
        Vector headers = new Vector();
        if (flag) {
            String sql = "select * from tb_bookInfo;";
            try {
                ResultSetMetaData head = Dao.executeQuery(sql).getMetaData();
                for (int i = 1; i <= head.getColumnCount(); i++) {
                    headers.addElement(head.getColumnName(i));

                }
                return headers;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Dao.close();
            return null;
        } else {
            headers.addElement("ISBN");
            headers.addElement("图书类别");
            headers.addElement("书名");
            headers.addElement("作者");
            headers.addElement("译者");
            headers.addElement("出版社");
            headers.addElement("出版日期");
            headers.addElement("价格");
            headers.addElement("借阅次数");
            headers.addElement("存量");
            headers.addElement("当前存量");
            return headers;
        }
    }

    //获取读者表头
    public static Vector ReaderHead(boolean flag) {
        Vector headers = new Vector();
        if (flag) {
            String sql = "select * from tb_reader;";
            try {
                ResultSetMetaData head = Dao.executeQuery(sql).getMetaData();
                for (int i = 1; i <= head.getColumnCount(); i++) {
                    headers.addElement(head.getColumnName(i));
                }
                return headers;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Dao.close();
            return null;
        } else {
            headers.addElement("读者证号");
            headers.addElement("姓名");
            headers.addElement("性别");
            headers.addElement("年龄");
            headers.addElement("证号");
            headers.addElement("当前借书量");
            headers.addElement("电话");
            headers.addElement("押金");
            headers.addElement("办证日期");
            return headers;
        }
    }

    //获取读者信息
    public static Vector ReaderColumns() {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.ReaderHead(true);
        String sql = "select * from tb_reader";
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 0; i < headerBook.size() - 1; i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //借阅表表头
    public static Vector borrowHead(boolean flag) {
        Vector headers = new Vector();
        if (flag) {
            String sql = "select * from tb_borrow;";
            try {
                ResultSetMetaData head = Dao.executeQuery(sql).getMetaData();
                for (int i = 1; i <= head.getColumnCount(); i++) {
                    headers.addElement(head.getColumnName(i));
                }
                return headers;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Dao.close();
            return null;
        } else {
            headers.addElement("ISBN");
            headers.addElement("借书管理员");
            headers.addElement("读者证号");
            headers.addElement("归还");
            headers.addElement("借书日期");
            headers.addElement("还书日期");
            headers.addElement("还书管理员");
            return headers;
        }
    }

    //借阅表内容
    public static Vector borrowColumns(String readerid) {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.borrowHead(true);
        String sql = "select * from tb_borrow where readerid='" + readerid + "';";
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 1; i < headerBook.size(); i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //获取图书表的记录
    public static Vector bookInfoColumns() {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.bookInfoHead(true);
        String sql = "select ISBN,typeName as typeId,bookname,writer,translator,publisher,date,price,count,all_count,cur_count from tb_bookInfo,tb_bookType where tb_bookInfo.typeId=tb_bookType.id;";
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 0; i < headerBook.size(); i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //获取图书表的列名
    public static Vector bookInfoColumns(String searchCondition, String searchText) {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.bookInfoHead(true);
        String sql = "select * from tb_bookInfo where " + searchCondition + " like '%" + searchText + "%';";
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 0; i < headerBook.size(); i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //是否有超期图书
    public static boolean hasNoBackbook(String readerId) {
        String selectSql = "select id from tb_borrow where isback=0 and readerId='" + readerId +
                "' and to_days(curdate()) - to_days(borrowDate)>" + maxDays + ";";
        ResultSet rs = Dao.executeQuery(selectSql);
        try {
            return rs.isAfterLast() != rs.isBeforeFirst();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return true;
    }

    //能否借阅
    public static int canBeBorrow(Borrow borrow) {
        //图书剩余
        String sql = "select cur_count from tb_bookInfo where ISBN='" + borrow.getISBN() + "';";
        //是否借过此图书
        String selectSql = "select id from tb_borrow where isback=0 and readerId='" + borrow.getReaderId() +
                "' and bookISBN='" + borrow.getISBN() + "';";
        //最大借书量
        String maxCountSql = "select maxNum from tb_reader where readerid='" + borrow.getReaderId() + "' and keepMoney > 0;";
        //有无此读者
        String readerSql = "select count(*) from tb_reader where readerid='" + borrow.getReaderId() + "';";
        ResultSet readerRs = Dao.executeQuery(readerSql);
        ResultSet rs = Dao.executeQuery(sql);
        ResultSet selectRs = Dao.executeQuery(selectSql);
        try {
            if (rs.next()) {
                if (readerRs.next()) {
                    if (readerRs.getInt(1) == 1) {
                        if (rs.getInt(1) > 0) {
                            if (selectRs.isAfterLast() == selectRs.isBeforeFirst()) {
                                ResultSet maxCount = Dao.executeQuery(maxCountSql);
                                if(maxCount.next()) {
                                    int flag=maxCount.getInt(1);
                                    rs.close();
                                    Dao.close();
                                    return  flag< 5 ? 5 : 6;
                                }else return 7;
                            } else return 4;
                        } else return 3;
                    } else return 2;
                } else return 1;
            } else return 0;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 1;
    }

    //借书
    public static boolean borrowBook(Borrow borrow) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String sql = "insert into tb_borrow (bookISBN,operatorId,readerid,isback,borrowDate) values ('" +
                borrow.getISBN() + "','" + borrow.getOperatorId() + "','" + borrow.getReaderId() + "'," +
                borrow.getIsBack() + ",'" + sdf.format(borrow.getBorrowDate()) + "');";
        String upsql = "update tb_bookInfo set cur_count=cur_count-1 ,count= count +1 where ISBN='" + borrow.getISBN() + "';";
        String maxSql = "update tb_reader set maxNum=maxNum+1 where readerid='" + borrow.getReaderId() + "';";
        int rs = Dao.executeUpdate(sql);
        int uprs = Dao.executeUpdate(upsql);
        int max = Dao.executeUpdate(maxSql);
        Dao.close();
        return rs > 0 && uprs > 0 && max > 0;
    }

    //能否还书
    public static int canBackBook(Borrow borrow) {
        String hasThisBookSql = "select ISBN from tb_bookInfo where ISBN='" + borrow.getISBN() + "';";
        String hasThisReaderSql = "select readerid from tb_reader where readerid='" + borrow.getReaderId() + "';";
        String hasBorrowThisBookSql = "select to_days(curdate()) - to_days(borrowDate) from tb_borrow where readerid='" +
                borrow.getReaderId() + "' and bookISBN='" + borrow.getISBN() + "' and isback=0;";
        String backBookSql = "update tb_bookInfo set cur_count = cur_count + 1 where ISBN='" +
                borrow.getISBN() + "';";
        String backBookUpSql = "update tb_borrow set isback=1 , backDate = curdate(), operatorid_back='" +
                borrow.getOperatorId_back() + "' where readerid='" + borrow.getReaderId() + "' and bookISBN='" + borrow.getISBN() + "' and isback=0;";
        String maxNumSql = "update tb_reader set maxNum=maxNum-1 where readerid='" + borrow.getReaderId() + "';";
        ResultSet hasThisBookRs = Dao.executeQuery(hasThisBookSql);
        ResultSet hasThisReaderRs = Dao.executeQuery(hasThisReaderSql);
        ResultSet hasBorrowThisBookRs = Dao.executeQuery(hasBorrowThisBookSql);

        try {
            if (hasThisBookRs.next()) {
                if (hasThisReaderRs.next()) {
                    if (hasBorrowThisBookRs.next()) {
                        if (hasBorrowThisBookRs.getInt(1) > maxDays) {
                            return hasBorrowThisBookRs.getInt(1);
                        } else return 3;
                    } else return 2;
                } else return 1;
            } else return 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 5;
    }

    //还书
    public static boolean backBook(Borrow borrow, int isOverDate) {
        String backBookSql = "update tb_bookInfo set cur_count = cur_count + 1 where ISBN='" +
                borrow.getISBN() + "';";
        String backBookUpSql = "update tb_borrow set isback=1 , backDate = curdate(), operatorid_back='" +
                borrow.getOperatorId_back() + "' where readerid='" + borrow.getReaderId() + "' and bookISBN='" +
                borrow.getISBN() + "' and isback=0;";
        String maxNumSql = "update tb_reader set maxNum=maxNum-1, keepMoney = keepMoney -" + yajin + " * " + isOverDate + " where readerid='" + borrow.getReaderId() + "';";
        int backBookRs = Dao.executeUpdate(backBookSql);
        int backBookUp = Dao.executeUpdate(backBookUpSql);
        int maxNum = Dao.executeUpdate(maxNumSql);
        return backBookRs > 0 && backBookUp > 0 && maxNum > 0;
    }

    public static boolean addNewReader(Reader reader) {
        String insertSql = "insert into tb_reader values (" + reader.toString() + ");";
        String updateSql = "update tb_reader set name='" + reader.getName() + "', sex='" + reader.getSex() + "',age=" +
                reader.getAge() + ", identityCard='" + reader.getIdentityCard() + "',tel='" + reader.getTel() + "',keepMoney=" +
                reader.getKeepMoney() + ",idtype=" + reader.getIdtype() + " where readerid='" + reader.getReaderid() + "';";
        String firstSql = "select count(*) from tb_reader where readerid='" + reader.getReaderid() + "';";
        ResultSet rs = Dao.executeQuery(firstSql);
        int flag = -1;
        try {
            while (rs.next()) {
                flag = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (flag == 0) {
            flag = Dao.executeUpdate(insertSql);
        } else {
            flag = Dao.executeUpdate(updateSql);
        }
        Dao.close();
        return flag > 0;
    }

    //设置借书天数和逾期罚款金额
    public static boolean setBorrowSettings(String maxNum, String yajin, String maxDays) {
        try {
            //确保是数值
            Dao.maxNum = Integer.parseInt(maxNum);
            Dao.yajin = Double.parseDouble(yajin);
            Dao.maxDays = Integer.parseInt(maxDays);
            String sql = "update tb_borrow_settings set maxNum=" + maxNum + ", yajin=" + yajin + ", maxDays=" + maxDays;
            int flag = Dao.executeUpdate(sql);
            Dao.close();
            return flag > 0;
        } catch (NumberFormatException e) {
            e.printStackTrace();
            return false;
        }
    }

    //获取借书设置信息
    public static void showBorrowSettings(JTextField maxNum, JTextField yajin, JTextField maxDays) {
        String sql = "select maxNum,yajin,maxDays from tb_borrow_settings;";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (rs.next()) {
                maxNum.setText(rs.getString(1));
                yajin.setText(rs.getString(2));
                maxDays.setText(rs.getString(3));
            }
            Dao.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //获得下架图书信息
    public static boolean xiaJia(String ISBN) {
        String sql = "select * from tb_bookInfo where ISBN='" + ISBN + "';";
        ResultSet rs = Dao.executeQuery(sql);
        BookInfo bookInfo = new BookInfo();
        try {
            if (rs.next()) {
                bookInfo.setISBN(rs.getString(1));
                bookInfo.setTypeid(rs.getInt(2));
                bookInfo.setBookname(rs.getString(3));
                bookInfo.setWriter(rs.getString(4));
                bookInfo.setTranslator(rs.getString(5));
                bookInfo.setPublisher(rs.getString(6));
                bookInfo.setDate(rs.getDate(7));
                bookInfo.setPrice(rs.getDouble(8));
                bookInfo.setCount(rs.getInt(9));
                bookInfo.setAll_count(rs.getInt(10));

                sql = "delete from tb_bookInfo where ISBN='" + ISBN + "';";
                int flag = Dao.executeUpdate(sql);
                if (flag > 0) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                    String dateText = sdf.format(bookInfo.getDate());
                    sql = "insert into tb_bookInfo_old values ('" + bookInfo.getISBN() + "'," + bookInfo.getTypeid() +
                            ",'" + bookInfo.getBookname() + "','" + bookInfo.getWriter() + "','" + bookInfo.getTranslator() +
                            "','" + bookInfo.getPublisher() + "','" + dateText + "'," + bookInfo.getPrice() + "," + bookInfo.getCount() + ","
                            + bookInfo.getAll_count() + ");";
                    flag = Dao.executeUpdate(sql);
                    return flag > 0;
                }
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static Vector bookOldInfoHeader(boolean flag) {
        Vector headers = new Vector();
        if (flag) {
            String sql = "select * from tb_bookInfo_old;";
            try {
                ResultSetMetaData head = Dao.executeQuery(sql).getMetaData();
                for (int i = 1; i <= head.getColumnCount() - 1; i++) {
                    headers.addElement(head.getColumnName(i));

                }
                return headers;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Dao.close();
            return null;
        } else {
            headers.addElement("ISBN");
            headers.addElement("图书类别");
            headers.addElement("书名");
            headers.addElement("作者");
            headers.addElement("译者");
            headers.addElement("出版社");
            headers.addElement("出版日期");
            headers.addElement("价格");
            headers.addElement("借阅次数");
            headers.addElement("存量");
            return headers;
        }
    }

    public static Vector bookOldInfoColumns() {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.bookOldInfoHeader(true);
        String sql = "select ISBN,typeName as typeId,bookname,writer,translator,publisher,date,price,count,all_count from tb_bookInfo_old,tb_bookType where tb_bookInfo_old.typeId=tb_bookType.id;";
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 0; i < headerBook.size(); i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //上架操作
    public static boolean shangJia(String ISBN) {
        String sql = "select * from tb_bookInfo_old where ISBN='" + ISBN + "';";
        ResultSet rs = Dao.executeQuery(sql);
        BookInfo bookInfo = new BookInfo();
        try {
            if (rs.next()) {
                bookInfo.setISBN(rs.getString(1));
                bookInfo.setTypeid(rs.getInt(2));
                bookInfo.setBookname(rs.getString(3));
                bookInfo.setWriter(rs.getString(4));
                bookInfo.setTranslator(rs.getString(5));
                bookInfo.setPublisher(rs.getString(6));
                bookInfo.setDate(rs.getDate(7));
                bookInfo.setPrice(rs.getDouble(8));
                bookInfo.setCount(rs.getInt(9));
                bookInfo.setAll_count(rs.getInt(10));

                sql = "delete from tb_bookInfo_old where ISBN='" + ISBN + "';";
                Dao.executeUpdate(sql);
                return Dao.addNewBook(bookInfo);//上架图书
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //获取管理员信息
    public static Operater selectOperator(String adminId) {
        String sql = "select * from tb_operator where operator_id='" + adminId + "';";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (rs.next()) {
                Operater operater = new Operater();
                operater.setId(rs.getString(1));
                operater.setName(rs.getString(2));
                operater.setGrade(rs.getInt(3));
                operater.setPassword(rs.getString(4));
                return operater;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean editOperator(Operater operater) {
        String sql = "update tb_operator set name='" + operater.getName() + "', admin=" + operater.getGrade() +
                ",password='" + operater.getPassword() + "' where operator_id='" + operater.getId() + "';";
        int flag = Dao.executeUpdate(sql);
        return flag > 0;
    }

    public static BookInfo selectOrder(String ISBN) {
        BookInfo bookInfo = new BookInfo();
        String sql = "select * from tb_order_book where ISBN like '" + ISBN + "';";
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (!rs.next()) {
                return null;
            }
            bookInfo.setTypeid(rs.getInt("typeId"));
            bookInfo.setBookname(rs.getString("bookname"));
            bookInfo.setWriter((rs.getString("writer")));
            bookInfo.setTranslator(rs.getString("translator"));
            bookInfo.setPublisher(rs.getString("publisher"));
            bookInfo.setDate(rs.getDate("date"));
            bookInfo.setPrice(rs.getDouble("price"));
            bookInfo.setAll_count(rs.getInt("count"));
            //bookInfo.setCur_count(rs.getInt("cur_count"));
            rs.close();
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
        }
        Dao.close();
        return bookInfo;
    }

    //修改订单信息
    public static boolean EditOrder(BookInfo bookInfo) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String dateText = sdf.format(bookInfo.getDate());
        String updateSql = "update tb_order_book set bookname='" + bookInfo.getBookname() + "',writer='" +
                bookInfo.getWriter() + "',translator='" + bookInfo.getTranslator() + "',publisher='" +
                bookInfo.getPublisher() + "',date='" + dateText + "',price=" + bookInfo.getPrice() +
                ",count=" + bookInfo.getAll_count() + " where ISBN='" + bookInfo.getISBN() + "';";
        int flag = Dao.executeUpdate(updateSql);
        Dao.close();
        return flag > 0;
    }

    //获取订单表头
    public static Vector orderInfoHead(boolean flag) {
        Vector headers = new Vector();
        if (flag) {
            String sql = "select * from tb_order_book;";
            try {
                ResultSetMetaData head = Dao.executeQuery(sql).getMetaData();
                for (int i = 1; i <= head.getColumnCount() - 1; i++) {
                    headers.addElement(head.getColumnName(i));

                }
                return headers;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Dao.close();
            return null;
        } else {
            headers.addElement("ISBN");
            headers.addElement("图书类别");
            headers.addElement("书名");
            headers.addElement("作者");
            headers.addElement("译者");
            headers.addElement("出版社");
            headers.addElement("出版日期");
            headers.addElement("价格");
            headers.addElement("总量");

            return headers;
        }
    }

    //获取订单表内容
    public static Vector orderInfoColumns(boolean flag) {
        Vector columns;
        Vector result = new Vector();
        Vector headerBook = Dao.orderInfoHead(true);
        String sql = "select ISBN,typeName as typeId,bookname,writer,translator,publisher,date,price,count,count from tb_order_book,tb_bookType where tb_order_book.typeId=tb_bookType.id";
        if (flag) {
            sql += " and flag=1";
        }
        ResultSet rs = Dao.executeQuery(sql);
        try {

            while (rs.next()) {
                columns = new Vector();
                for (int i = 0; i < headerBook.size(); i++) {
                    columns.addElement(rs.getString((String) headerBook.get(i)));
                }
                result.addElement(columns);
            }
            rs.close();
            Dao.close();
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return null;
        }
        return result;
    }

    //订单到货
    public static boolean orderArrived(String ISBN, boolean isruku) {
        String sql = "update tb_order_book set flag=1 where ISBN='" + ISBN + "' and flag=0;";
        boolean isRuku = false;
        int flag = Dao.executeUpdate(sql);
        if (isruku) {
            String addSql = "select * from tb_order_book where ISBN='" + ISBN + "' and flag=1;";
            ResultSet rs = Dao.executeQuery(addSql);
            try {
                if (rs.next()) {
                    BookInfo bookInfo = new BookInfo();
                    bookInfo.setISBN(ISBN);
                    bookInfo.setTypeid(rs.getInt("typeId"));
                    bookInfo.setBookname(rs.getString("bookname"));
                    bookInfo.setWriter((rs.getString("writer")));
                    bookInfo.setTranslator(rs.getString("translator"));
                    bookInfo.setPublisher(rs.getString("publisher"));
                    bookInfo.setDate(rs.getDate("date"));
                    bookInfo.setPrice(rs.getDouble("price"));
                    bookInfo.setAll_count(rs.getInt("count"));
                    isRuku = Dao.addNewBook(bookInfo);
                    if (isRuku) {
                        return Dao.executeUpdate("delete from tb_order_book where ISBN='" + ISBN + "';") > 0;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return flag > 0;
    }

    public static Reader selectReader(String readerid) {
        String sql = "select * from tb_reader where readerid='" + readerid + "';";
        Reader reader = new Reader();
        ResultSet rs = Dao.executeQuery(sql);
        try {
            if (rs.next()) {
                reader.setReaderid(readerid);
                reader.setName(rs.getString(2));
                reader.setSex(rs.getString(3));
                reader.setAge(rs.getInt(4));
                reader.setIdentityCard(rs.getString(5));
                reader.setTel(rs.getString(7));
                reader.setKeepMoney(rs.getDouble(8));
                reader.setIdtype(rs.getInt(9));
                reader.setIdtype(rs.getInt(10));
                return reader;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //nene专属方法

    void executeUpdate() {
        try {
            if (conn == null) {                     //如果连接对象为空
                Class.forName(dbClassName);         //加载驱动类
                conn = DriverManager.getConnection
                        (dbUrl, dbUser, dbPwd);   //获得连接对象
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        try {
            sql = conn.createStatement();
        } catch (SQLException e) {
            System.out.println("声明sql语句对象失败！ " + e.getMessage());
        }
        //System.out.println(getStatement("update"));
        try {
            sql.execute(getStatement("update"));
        } catch (SQLException e) {
            System.out.println("执行sql语句失败！ " + e.getMessage());
        }
    }

    //insert into 表名(列名)values(值)
    public static int executeInsert() {
        try {
            if (conn == null) {                     //如果连接对象为空
                Class.forName(dbClassName);         //加载驱动类
                conn = DriverManager.getConnection
                        (dbUrl, dbUser, dbPwd);   //获得连接对象
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        try {
            sql = conn.createStatement();
        } catch (SQLException e) {
            System.out.println("声明sql语句对象失败！ " + e.getMessage());
        }
        try {
            sql.execute(getStatement("insert"));
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(new JPanel(), e.getMessage(), "提示", JOptionPane.WARNING_MESSAGE);
            return 0;
        }
        return 1;
    }

    /*
     * 下列方法用于分项处理sql语句
     * */
    //setColumns()方法用于设置查询列
    public static void setColumns(String[] c) {
        columns = c;
    }

    //getColumns()组合列
    private static String getColumns() {
        String c = "";
        if (columns == null) {
            return "*";
        } else {
            for (String str : columns) {
                c = c + str + ",";
            }
            c = c.substring(0, c.length() - 1);
        }
        return c;
    }

    //setForms()方法用于设置查询表
    public static void setForms(String[] f) {
        forms = f;
    }

    //getForms()方法用于组合查询表
    private static String getForms() {
        String c = "";
        if (forms == null) {
            System.out.println("必须指定表名");
        } else {
            for (String str : forms) {
                c = c + str + ",";
            }
            c = c.substring(0, c.length() - 1);
        }
        return c;
    }

    //setWhere()用于设置查询条件
    void setWhere(String where) {
        Dao.where = where;
    }

    //setNewKV()用于设置 update语句中的键对值
    void setNewKV(String KV) {
        newKV = KV;
    }

    //setValues()用于设置insert中的值
    public static void setValues(String[] v) {
        values = v;
    }

    //getValues()用于组合insert中的值
    private static String getValues() {
        String c = "";
        if (values == null) {
            System.out.println("必须设置值！");
        } else {
            for (String str : values) {
                c = c + '"' + str + '"' + ",";
            }
            c = c.substring(0, c.length() - 1);
        }
        return c;
    }

    /*
     * getStatement()方法用于组合sql语句
     * */
    private static String getStatement(String type) {
        String statement = null;//statement存储sql语句
        if (type == "select") {
            statement = "select " + getColumns() + " from " + getForms();
            if (where != null) {
                statement = statement + " where " + where;
            }
        }
        if (type == "update") {
            statement = "update " + getForms() + " set " + newKV + " where " + where;
        }
        if (type == "insert") {
            statement = "insert into " + getForms() + "(" + getColumns() + ") values (" + getValues() + ")";
        }
        return statement;
    }

}